﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace ExcelImport
{
    public class DoWork
    {
        public DoWork()
        {
        }
        private string fileName = null; //文件名
        private IWorkbook workbook = null;
        private FileStream fs = null;
        private static string time = string.Empty;


        public int RetrieveData(BackgroundWorker worker, DoWorkEventArgs e)
        {
            Log log = new Log(AppDomain.CurrentDomain.BaseDirectory + @"/log/Log.txt");

            int percent = 0;
            fileName = (string)e.Argument;
            int maxRecords = 0;
            ISheet sheet = null;
            int startRow = 0;
            try
            {
                string file = System.IO.Path.GetFileNameWithoutExtension(fileName);
                string insertsql = string.Empty;
                string insertsql1 = string.Empty;
                int tag;

                switch (file)
                {
                    case "案卷级汇总":
                        log.log("开始导入：" + fileName);
                        fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                        if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                            workbook = new XSSFWorkbook(fs);
                        else if (fileName.IndexOf(".xls") > 0) // 2003版本
                            workbook = new HSSFWorkbook(fs);

                        sheet = workbook.GetSheetAt(0);
                        if (sheet != null)
                        {
                            IRow firstRow = sheet.GetRow(0);
                            int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                            startRow = sheet.FirstRowNum + 1;


                            //最后一行的标号
                            int rowCount = sheet.LastRowNum;
                            maxRecords = rowCount;
                            time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                            for (int i = startRow; i <= rowCount; ++i)
                            {
                                if (worker.CancellationPending || (i - 1) == rowCount)
                                {
                                    return i - 1;
                                }
                                percent = (int)(((double)i / (double)rowCount) * 100);

                                IRow row = sheet.GetRow(i);
                                if (row == null) continue; //没有数据的行默认是null　　　　　　　

                                List<string> list = new List<string>();
                                for (int j = row.FirstCellNum; j < cellCount; ++j)
                                {
                                    if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                        list.Add(row.GetCell(j).ToString());
                                    else
                                        list.Add("");
                                }
                                string STORAGE_LOCATION = list[7];
                                string DOC_CABINET = string.Empty;
                                string DOC_GRID = string.Empty;
                                if (list[7].Split('排').Length > 1)
                                {
                                    DOC_CABINET = list[7].Split('排')[0] + "排";
                                    DOC_GRID = list[7].Split('排')[1];
                                }
                                string querysql = string.Format(@"SELECT ID FROM erms_storage WHERE STORAGE_LOCATION='{0}'", STORAGE_LOCATION);
                                SQLHelper sqlhelper = new SQLHelper();
                                DataTable storage = sqlhelper.FindDataTable(querysql, null);
                                var Snowflakeworker = new IdWorker(1, 1);
                                string ERMS_DOSSIER_ID = Snowflakeworker.NextId().ToString();//案卷ID
                                string ERMS_AUDIT_TIMES_ID = Snowflakeworker.NextId().ToString();//档案/案卷审核记录表ID
                                string ERMS_STORAGE_ID = string.Empty;//档案库房表ID
                                int SAVE_PERIOD;
                                switch (list[5].Trim())
                                {
                                    case "永久": SAVE_PERIOD = 1; break;
                                    case "定期10年": SAVE_PERIOD = 2; break;
                                    case "定期30年": SAVE_PERIOD = 3; break;
                                    default: SAVE_PERIOD = 0; break;
                                }
                                int SECRET;
                                switch (list[6].Trim())
                                {
                                    case "秘密": SECRET = 1; break;
                                    case "机密": SECRET = 2; break;
                                    case "绝密": SECRET = 3; break;
                                    default: SECRET = 0; break;
                                }
                                string CUR_DATE = string.Empty;
                                if (string.IsNullOrEmpty(list[3]))
                                {
                                    CUR_DATE = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                                }
                                else
                                {
                                    if (list[3].Length < 8)
                                    {
                                        CUR_DATE = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                                    }
                                    else
                                    {
                                        CUR_DATE = list[3] + " 00:00:00";
                                    }
                                    //CUR_DATE = list[3].Substring(0, 4) + "-" + list[3].Substring(4, 2) + "-01 00:00:00";
                                }
                                try
                                {
                                    if (storage != null && storage.Rows.Count > 0)
                                    {
                                        ERMS_STORAGE_ID = storage.Rows[0][0].ToString();
                                        #region 注释
                                        //insertsql = string.Format(@"INSERT INTO erms_dossier_ru (
                                        //                                    ID,
                                        //                                    CREATE_USER,
                                        //                                    MODIFY_USER,
                                        //                                    DOSSIER_NO,
                                        //                                    DOSSIER_NAME,
                                        //                                    ERMS_SECT_ID,
                                        //                                    ERMS_ARCH_TYPE_ID,
                                        //                                    ERMS_STORAGE_ID,
                                        //                                    ERMS_AUDIT_TIMES_ID,
                                        //                                    APPLY_LINE,
                                        //                                    CUR_OPER,
                                        //                                    CUR_DATE,
                                        //                                    SAVE_PERIOD,
                                        //                                    SECRET,
                                        //                                    NOTES
                                        //                                    )
                                        //                                    VALUES
                                        //                                    ('{0}','{1}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{1}','{9}','{10}','{11}','{12}');
                                        //                                    INSERT INTO erms_audit_times (
                                        //                                    ID,
                                        //                                    CREATE_USER,
                                        //                                    MODIFY_USER,
                                        //                                    ERMS_ARCH_FORM_ID,
                                        //                                    ERMS_DOSSIER_ID,
                                        //                                    AUDIT_STATUS,
                                        //                                    APPROVAL_DATE,
                                        //                                    AUDIT_TYPE,
                                        //                                    ARCH_NUM
                                        //                                    )
                                        //                                    VALUES
                                        //                                    ('{13}', '{1}', '{1}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}');",
                                        //                                    ERMS_DOSSIER_ID, "Admin", !string.IsNullOrEmpty(list[0].Trim()) ? list[0] : Guid.NewGuid().ToString(), list[1], "1138013309136670720",
                                        //                                    "1138013519845920768", ERMS_STORAGE_ID, ERMS_AUDIT_TIMES_ID, 50, CUR_DATE, SAVE_PERIOD, SECRET, list[9],
                                        //                                    ERMS_AUDIT_TIMES_ID, "", ERMS_DOSSIER_ID, 1, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), 60, 1);
                                        #endregion
                                        insertsql1 = string.Format(@"INSERT INTO erms_dossier_ru (
                                                                        ID,
                                                                        CREATE_USER,
                                                                        MODIFY_USER,
                                                                        DOSSIER_NO,
                                                                        DOSSIER_NAME,
                                                                        ERMS_SECT_ID,
                                                                        ERMS_ARCH_TYPE_ID,
                                                                        ERMS_STORAGE_ID,
                                                                        ERMS_AUDIT_TIMES_ID,
                                                                        APPLY_LINE,
                                                                        CUR_OPER,
                                                                        CUR_DATE,
                                                                        SAVE_PERIOD,
                                                                        SECRET,
                                                                        NOTES
                                                                        )
                                                                        VALUES
                                                                        (@ERMS_DOSSIER_ID,@CREATE_USER,@MODIFY_USER,@DOSSIER_NO,@DOSSIER_NAME,@ERMS_SECT_ID,@ERMS_ARCH_TYPE_ID,@ERMS_STORAGE_ID,@ERMS_AUDIT_TIMES_ID,@APPLY_LINE,@CUR_OPER,@CUR_DATE,@SAVE_PERIOD,@SECRET,@NOTES);
                                                                        INSERT INTO erms_audit_times (
                                                                        ID,
                                                                        CREATE_USER,
                                                                        MODIFY_USER,
                                                                        ERMS_ARCH_FORM_ID,
                                                                        ERMS_DOSSIER_ID,
                                                                        AUDIT_STATUS,
                                                                        APPROVAL_DATE,
                                                                        AUDIT_TYPE,
                                                                        ARCH_NUM
                                                                        )
                                                                        VALUES
                                                                        (@ERMS_AUDIT_TIMES_ID, @CREATE_USER, @MODIFY_USER, @ERMS_ARCH_FORM_ID, @ERMS_DOSSIER_ID, @AUDIT_STATUS, @APPROVAL_DATE, @AUDIT_TYPE, @ARCH_NUM);");
                                        DynamicParameters para = new DynamicParameters();
                                        para.Add("ERMS_DOSSIER_ID", ERMS_DOSSIER_ID);
                                        para.Add("CREATE_USER", "Admin");
                                        para.Add("MODIFY_USER", "Admin");
                                        para.Add("DOSSIER_NO", !string.IsNullOrEmpty(list[0].Trim()) ? list[0] : Guid.NewGuid().ToString());
                                        para.Add("DOSSIER_NAME", list[1]);
                                        para.Add("ERMS_SECT_ID", "1138013309136670720");
                                        para.Add("ERMS_ARCH_TYPE_ID", "1138013519845920768");
                                        para.Add("ERMS_STORAGE_ID", ERMS_STORAGE_ID);
                                        para.Add("ERMS_AUDIT_TIMES_ID", ERMS_AUDIT_TIMES_ID);
                                        para.Add("APPLY_LINE", 50);
                                        para.Add("CUR_OPER", "Admin");
                                        para.Add("CUR_DATE", CUR_DATE);
                                        para.Add("SAVE_PERIOD", SAVE_PERIOD);
                                        para.Add("SECRET", SECRET);
                                        para.Add("NOTES", list[9]);
                                        para.Add("ERMS_ARCH_FORM_ID", "");
                                        para.Add("AUDIT_STATUS", 1);
                                        para.Add("APPROVAL_DATE", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                                        para.Add("AUDIT_TYPE", 60);
                                        para.Add("ARCH_NUM", 1);
                                        tag = sqlhelper.ExcuteNonQuery(insertsql1, para);

                                    }
                                    else
                                    {
                                        ERMS_STORAGE_ID = Snowflakeworker.NextId().ToString();
                                        #region 注释
                                        //insertsql = string.Format(@"INSERT INTO erms_storage (
                                        //                                    ID,
                                        //                                    CREATE_USER,
                                        //                                    MODIFY_USER,
                                        //                                    STORAGE_LOCATION,
                                        //                                    DOC_CABINET,
                                        //                                    DOC_GRID
                                        //                                    )
                                        //                                    VALUES
                                        //                                    ('{0}','{1}','{1}','{2}','{3}','{4}');
                                        //                                    INSERT INTO erms_dossier_ru (
                                        //                                    ID,
                                        //                                    CREATE_USER,
                                        //                                    MODIFY_USER,
                                        //                                    DOSSIER_NO,
                                        //                                    DOSSIER_NAME,
                                        //                                    ERMS_SECT_ID,
                                        //                                    ERMS_ARCH_TYPE_ID,
                                        //                                    ERMS_STORAGE_ID,
                                        //                                    ERMS_AUDIT_TIMES_ID,
                                        //                                    APPLY_LINE,
                                        //                                    CUR_OPER,
                                        //                                    CUR_DATE,
                                        //                                    SAVE_PERIOD,
                                        //                                    SECRET,
                                        //                                    NOTES
                                        //                                    )
                                        //                                    VALUES
                                        //                                    ('{5}','{1}','{1}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{1}','{13}','{14}','{15}','{16}');
                                        //                                    INSERT INTO erms_audit_times (
                                        //                                    ID,
                                        //                                    CREATE_USER,
                                        //                                    MODIFY_USER,
                                        //                                    ERMS_ARCH_FORM_ID,
                                        //                                    ERMS_DOSSIER_ID,
                                        //                                    AUDIT_STATUS,
                                        //                                    APPROVAL_DATE,
                                        //                                    AUDIT_TYPE,
                                        //                                    ARCH_NUM
                                        //                                    )
                                        //                                    VALUES
                                        //                                    ('{17}', '{1}', '{1}', '{18}', '{19}', '{20}', '{21}', '{22}', '{23}');",
                                        //                                    ERMS_STORAGE_ID, "Admin", list[7], DOC_CABINET, DOC_GRID, ERMS_DOSSIER_ID, !string.IsNullOrEmpty(list[0].Trim()) ? list[0] : Guid.NewGuid().ToString(),
                                        //                                    list[1], "1138013309136670720", "1138013519845920768", ERMS_STORAGE_ID, ERMS_AUDIT_TIMES_ID, 50, CUR_DATE, SAVE_PERIOD, SECRET, list[9],
                                        //                                    ERMS_AUDIT_TIMES_ID, "", ERMS_DOSSIER_ID, 1, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), 60, 1);
                                        #endregion
                                        insertsql1 = string.Format(@"INSERT INTO erms_storage (
                                                                        ID,
                                                                        CREATE_USER,
                                                                        MODIFY_USER,
                                                                        STORAGE_LOCATION,
                                                                        DOC_CABINET,
                                                                        DOC_GRID
                                                                        )
                                                                        VALUES
                                                                        (@ERMS_STORAGE_ID,@CREATE_USER,@MODIFY_USER,@STORAGE_LOCATION,@DOC_CABINET,@DOC_GRID);
                                                                        INSERT INTO erms_dossier_ru (
                                                                        ID,
                                                                        CREATE_USER,
                                                                        MODIFY_USER,
                                                                        DOSSIER_NO,
                                                                        DOSSIER_NAME,
                                                                        ERMS_SECT_ID,
                                                                        ERMS_ARCH_TYPE_ID,
                                                                        ERMS_STORAGE_ID,
                                                                        ERMS_AUDIT_TIMES_ID,
                                                                        APPLY_LINE,
                                                                        CUR_OPER,
                                                                        CUR_DATE,
                                                                        SAVE_PERIOD,
                                                                        SECRET,
                                                                        NOTES
                                                                        )
                                                                        VALUES
                                                                        (@ERMS_DOSSIER_ID,@CREATE_USER,@MODIFY_USER,@DOSSIER_NO,@DOSSIER_NAME,@ERMS_SECT_ID,@ERMS_ARCH_TYPE_ID,@ERMS_STORAGE_ID,@ERMS_AUDIT_TIMES_ID,@APPLY_LINE,@CUR_OPER,@CUR_DATE,@SAVE_PERIOD,@SECRET,@NOTES);
                                                                        INSERT INTO erms_audit_times (
                                                                        ID,
                                                                        CREATE_USER,
                                                                        MODIFY_USER,
                                                                        ERMS_ARCH_FORM_ID,
                                                                        ERMS_DOSSIER_ID,
                                                                        AUDIT_STATUS,
                                                                        APPROVAL_DATE,
                                                                        AUDIT_TYPE,
                                                                        ARCH_NUM
                                                                        )
                                                                        VALUES
                                                                        (@ERMS_AUDIT_TIMES_ID, @CREATE_USER, @MODIFY_USER, @ERMS_ARCH_FORM_ID, @ERMS_DOSSIER_ID, @AUDIT_STATUS, @APPROVAL_DATE, @AUDIT_TYPE, @ARCH_NUM);");
                                        DynamicParameters para = new DynamicParameters();
                                        para.Add("STORAGE_LOCATION", list[7]);
                                        para.Add("DOC_CABINET", DOC_CABINET);
                                        para.Add("DOC_GRID", DOC_GRID);
                                        para.Add("ERMS_DOSSIER_ID", ERMS_DOSSIER_ID);
                                        para.Add("CREATE_USER", "Admin");
                                        para.Add("MODIFY_USER", "Admin");
                                        para.Add("DOSSIER_NO", !string.IsNullOrEmpty(list[0].Trim()) ? list[0] : Guid.NewGuid().ToString());
                                        para.Add("DOSSIER_NAME", list[1]);
                                        para.Add("ERMS_SECT_ID", "1138013309136670720");
                                        para.Add("ERMS_ARCH_TYPE_ID", "1138013519845920768");
                                        para.Add("ERMS_STORAGE_ID", ERMS_STORAGE_ID);
                                        para.Add("ERMS_AUDIT_TIMES_ID", ERMS_AUDIT_TIMES_ID);
                                        para.Add("APPLY_LINE", 50);
                                        para.Add("CUR_OPER", "Admin");
                                        para.Add("CUR_DATE", CUR_DATE);
                                        para.Add("SAVE_PERIOD", SAVE_PERIOD);
                                        para.Add("SECRET", SECRET);
                                        para.Add("NOTES", list[9]);
                                        para.Add("ERMS_ARCH_FORM_ID", "");
                                        para.Add("AUDIT_STATUS", 1);
                                        para.Add("APPROVAL_DATE", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                                        para.Add("AUDIT_TYPE", 60);
                                        para.Add("ARCH_NUM", 1);
                                        tag = sqlhelper.ExcuteNonQuery(insertsql1, para);
                                    }
                                    //DynamicParameters param = new DynamicParameters();
                                    //int tag = sqlhelper.ExcuteNonQuery(insertsql, param);
                                    if (tag < 1)
                                    {
                                        log.log("第" + i + "行导入失败！");
                                    }
                                    Model m = new Model();
                                    m.CREATETIME = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                                    m.STATUS = (tag > 0 ? "成功" : "失败");
                                    m.ID = list[0];
                                    m.NAME = list[1];
                                    Result r = new Result();
                                    r.m = m;
                                    r.count = i.ToString();
                                    worker.ReportProgress(percent, r);
                                    //worker.ReportProgress(percent);
                                    //Thread.Sleep(100);
                                }
                                catch (Exception ex)
                                {
                                    log.log("第" + i + "行导入失败！" + ex.Message);
                                    Model m = new Model();
                                    m.CREATETIME = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                                    m.STATUS = "失败";
                                    m.ID = list[0];
                                    m.NAME = list[1];
                                    Result r = new Result();
                                    r.m = m;
                                    r.count = i.ToString();
                                    worker.ReportProgress(percent, r);
                                }

                                continue;
                            }
                        }
                        log.log("导入完成！");
                        break;
                    case "卷内目录汇总":
                        log.log("开始导入：" + fileName);
                        fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                        if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                            workbook = new XSSFWorkbook(fs);
                        else if (fileName.IndexOf(".xls") > 0) // 2003版本
                            workbook = new HSSFWorkbook(fs);

                        sheet = workbook.GetSheetAt(0);
                        if (sheet != null)
                        {
                            IRow firstRow = sheet.GetRow(0);
                            int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                            startRow = sheet.FirstRowNum + 1;

                            //IRow row1 = sheet.GetRow(1);
                            //IRow row2 = sheet.GetRow(2);
                            //IRow row3 = sheet.GetRow(3);
                            //最后一行的标号
                            int rowCount = sheet.LastRowNum;
                            maxRecords = rowCount;
                            time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                            for (int i = startRow; i <= rowCount; ++i)
                            {
                                if (worker.CancellationPending || (i - 1) == rowCount)
                                {
                                    return i - 1;
                                }
                                percent = (int)(((double)i / (double)rowCount) * 100);

                                IRow row = sheet.GetRow(i);
                                if (row == null) continue; //没有数据的行默认是null　　　　　　　

                                List<string> list = new List<string>();
                                for (int j = row.FirstCellNum; j < cellCount; ++j)
                                {
                                    if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                        list.Add(row.GetCell(j).ToString());
                                    else
                                        list.Add("");
                                }
                                string STORAGE_LOCATION = list[10];
                                string DOC_CABINET = string.Empty;
                                string DOC_GRID = string.Empty;
                                if (list[10].Split('排').Length > 1)
                                {
                                    DOC_CABINET = list[10].Split('排')[0] + "排";
                                    DOC_GRID = list[10].Split('排')[1];
                                }
                                string querysql = string.Format(@"SELECT ID FROM erms_storage WHERE STORAGE_LOCATION='{0}'", STORAGE_LOCATION);
                                SQLHelper sqlhelper = new SQLHelper();
                                DataTable storage = sqlhelper.FindDataTable(querysql, null);
                                var Snowflakeworker = new IdWorker(1, 1);
                                string ERMS_ARCH_FORM_ID = Snowflakeworker.NextId().ToString();//档案基本属性表ID
                                string ERMS_AUDIT_TIMES_ID = Snowflakeworker.NextId().ToString();//档案/案卷审核记录表ID
                                string ERMS_ARCHIVED_STATUS_ID = Snowflakeworker.NextId().ToString();//档案归档状态表ID
                                string ERMS_DOSSIER_ID = string.Empty;//案卷ID
                                string ERMS_STORAGE_ID = string.Empty;//档案库房表ID
                                string sqlsel = string.Format(@"SELECT ID FROM erms_dossier_ru WHERE DOSSIER_NO='{0}'", list[0]);
                                DataTable dossier = sqlhelper.FindDataTable(sqlsel, null);
                                if (dossier != null && dossier.Rows.Count > 0)
                                {
                                    ERMS_DOSSIER_ID = dossier.Rows[0][0].ToString();
                                }
                                else
                                {
                                    ERMS_DOSSIER_ID = Guid.NewGuid().ToString();
                                }
                                int SAVE_PERIOD;
                                switch (list[8].Trim())
                                {
                                    case "永久": SAVE_PERIOD = 1; break;
                                    case "定期10年": SAVE_PERIOD = 2; break;
                                    case "定期30年": SAVE_PERIOD = 3; break;
                                    default: SAVE_PERIOD = 0; break;
                                }
                                int SECRET;
                                switch (list[9].Trim())
                                {
                                    case "秘密": SECRET = 1; break;
                                    case "机密": SECRET = 2; break;
                                    case "绝密": SECRET = 3; break;
                                    default: SECRET = 0; break;
                                }
                                string CUR_DATE = string.Empty;
                                if (string.IsNullOrEmpty(list[6]))
                                {
                                    CUR_DATE = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                                }
                                else
                                {
                                    if (list[6].Length < 8)
                                    {
                                        CUR_DATE = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                                    }
                                    else
                                    {
                                        CUR_DATE = list[6] + " 00:00:00";
                                    }
                                }
                                try
                                {
                                    if (storage != null && storage.Rows.Count > 0)
                                    {
                                        ERMS_STORAGE_ID = storage.Rows[0][0].ToString();
                                        #region 注释
                                        //insertsql = string.Format(@"INSERT INTO erms_arch_form_ru (
                                        //                                    ID,
                                        //                                    CREATE_USER,
                                        //                                    MODIFY_USER,
                                        //                                    ARCH_NO,
                                        //                                    ARCH_NAME,
                                        //                                    ERMS_SECT_ID,
                                        //                                    ERMS_ARCH_TYPE_ID,
                                        //                                    ERMS_DOSSIER_ID,
                                        //                                    ERMS_STORAGE_ID,
                                        //                                    ERMS_AUDIT_TIMES_ID,
                                        //                                    APPLY_LINE,
                                        //                                    CUR_OPER,
                                        //                                    CUR_DATE,
                                        //                                    SAVE_PERIOD,
                                        //                                    SECRET,
                                        //                                    CURRENT_ARCH_NUM,
                                        //                                    INIT_STOCK,
                                        //                                    NOTES
                                        //                                    )
                                        //                                    VALUES
                                        //                                    ('{0}','{1}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{1}','{10}','{11}','{12}','{13}','{14}','{15}');
                                        //                                    INSERT INTO erms_archived_status_ru (
                                        //                                    ID,
                                        //                                    CREATE_USER,
                                        //                                    MODIFY_USER,
                                        //                                    ERMS_ARCH_FORM_ID,
                                        //                                    ERMS_DOSSIER_ID,
                                        //                                    EVENT_STATUS,
                                        //                                    SAVE_DATE,
                                        //                                    SAVE_OPER,
                                        //                                    ES_HANDLE_STATUS
                                        //                                    )
                                        //                                    VALUES
                                        //                                    ('{16}','{1}','{1}','{0}','{6}','{17}','{10}','{1}','{18}');
                                        //                                    INSERT INTO erms_audit_times (
                                        //                                    ID,
                                        //                                    CREATE_USER,
                                        //                                    MODIFY_USER,
                                        //                                    ERMS_ARCH_FORM_ID,
                                        //                                    ERMS_DOSSIER_ID,
                                        //                                    AUDIT_STATUS,
                                        //                                    APPROVAL_DATE,
                                        //                                    AUDIT_TYPE,
                                        //                                    ARCH_NUM
                                        //                                    )
                                        //                                    VALUES
                                        //                                    ('{8}','{1}','{1}','{0}','{6}','{19}','{10}','{20}','{21}');",
                                        //                                    ERMS_ARCH_FORM_ID, "Admin", !string.IsNullOrEmpty(list[2].Trim()) ? list[2] : Guid.NewGuid().ToString(),
                                        //                                    list[5], "1138013309136670720", "1138013519845920768", ERMS_DOSSIER_ID, ERMS_STORAGE_ID, ERMS_AUDIT_TIMES_ID, 50, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                                        //                                    SAVE_PERIOD, SECRET, 1, 0, list[11], ERMS_ARCHIVED_STATUS_ID, 1, -1, 1, 60, 1);
                                        #endregion
                                        insertsql1 = string.Format(@"INSERT INTO erms_arch_form_ru (
                                                                        ID,
                                                                        CREATE_USER,
                                                                        MODIFY_USER,
                                                                        ARCH_NO,
                                                                        ARCH_NAME,
                                                                        ERMS_SECT_ID,
                                                                        ERMS_ARCH_TYPE_ID,
                                                                        ERMS_DOSSIER_ID,
                                                                        ERMS_STORAGE_ID,
                                                                        ERMS_AUDIT_TIMES_ID,
                                                                        APPLY_LINE,
                                                                        CUR_OPER,
                                                                        CUR_DATE,
                                                                        SAVE_PERIOD,
                                                                        SECRET,
                                                                        CURRENT_ARCH_NUM,
                                                                        INIT_STOCK,
                                                                        NOTES
                                                                        )
                                                                        VALUES
                                                                        (@ERMS_ARCH_FORM_ID,@CREATE_USER,@MODIFY_USER,@ARCH_NO,@ARCH_NAME,@ERMS_SECT_ID,@ERMS_ARCH_TYPE_ID,@ERMS_DOSSIER_ID,@ERMS_STORAGE_ID,@ERMS_AUDIT_TIMES_ID,@APPLY_LINE,@CUR_OPER,@CUR_DATE,@SAVE_PERIOD,@SECRET,@CURRENT_ARCH_NUM,@INIT_STOCK,@NOTES);
                                                                        INSERT INTO erms_archived_status_ru (
                                                                        ID,
                                                                        CREATE_USER,
                                                                        MODIFY_USER,
                                                                        ERMS_ARCH_FORM_ID,
                                                                        ERMS_DOSSIER_ID,
                                                                        EVENT_STATUS,
                                                                        SAVE_DATE,
                                                                        SAVE_OPER,
                                                                        ES_HANDLE_STATUS
                                                                        )
                                                                        VALUES
                                                                        (@ERMS_ARCHIVED_STATUS_ID,CREATE_USER,@MODIFY_USER,@ERMS_ARCH_FORM_ID,@ERMS_DOSSIER_ID,@EVENT_STATUS,@SAVE_DATE,@SAVE_OPER,@ES_HANDLE_STATUS);
                                                                        INSERT INTO erms_audit_times (
                                                                        ID,
                                                                        CREATE_USER,
                                                                        MODIFY_USER,
                                                                        ERMS_ARCH_FORM_ID,
                                                                        ERMS_DOSSIER_ID,
                                                                        AUDIT_STATUS,
                                                                        APPROVAL_DATE,
                                                                        AUDIT_TYPE,
                                                                        ARCH_NUM
                                                                        )
                                                                        VALUES
                                                                        (@ERMS_AUDIT_TIMES_ID,@CREATE_USER,@MODIFY_USER,@ERMS_ARCH_FORM_ID,@ERMS_DOSSIER_ID,@AUDIT_STATUS,@APPROVAL_DATE,@AUDIT_TYPE,@ARCH_NUM);");
                                        DynamicParameters param = new DynamicParameters();
                                        param.Add("ERMS_ARCH_FORM_ID", ERMS_ARCH_FORM_ID);
                                        param.Add("CREATE_USER", "Admin");
                                        param.Add("MODIFY_USER", "Admin");
                                        param.Add("ARCH_NO", !string.IsNullOrEmpty(list[2].Trim()) ? list[2] : Guid.NewGuid().ToString());
                                        param.Add("ARCH_NAME", list[5]);
                                        param.Add("ERMS_SECT_ID", "1138013309136670720");
                                        param.Add("ERMS_ARCH_TYPE_ID", "1138013519845920768");
                                        param.Add("ERMS_DOSSIER_ID", ERMS_DOSSIER_ID);
                                        param.Add("ERMS_STORAGE_ID", ERMS_STORAGE_ID);
                                        param.Add("ERMS_AUDIT_TIMES_ID", ERMS_AUDIT_TIMES_ID);
                                        param.Add("APPLY_LINE", 50);
                                        param.Add("CUR_OPER", "Admin");
                                        param.Add("CUR_DATE", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                                        param.Add("SAVE_PERIOD", SAVE_PERIOD);
                                        param.Add("SECRET", SECRET);
                                        param.Add("CURRENT_ARCH_NUM", 1);
                                        param.Add("INIT_STOCK", 0);
                                        param.Add("NOTES", list[11]);
                                        param.Add("ERMS_ARCHIVED_STATUS_ID", ERMS_ARCHIVED_STATUS_ID);
                                        param.Add("EVENT_STATUS", 1);
                                        param.Add("SAVE_DATE", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                                        param.Add("SAVE_OPER", "Admin");
                                        param.Add("ES_HANDLE_STATUS", -1);
                                        param.Add("ERMS_AUDIT_TIMES_ID", ERMS_AUDIT_TIMES_ID);
                                        param.Add("AUDIT_STATUS", 1);
                                        param.Add("APPROVAL_DATE", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                                        param.Add("AUDIT_TYPE", 60);
                                        param.Add("ARCH_NUM", 1);
                                        tag = sqlhelper.ExcuteNonQuery(insertsql1, param);
                                    }
                                    else
                                    {
                                        ERMS_STORAGE_ID = Snowflakeworker.NextId().ToString();
                                        #region 注释
                                        //insertsql = string.Format(@"INSERT INTO erms_storage (
                                        //                                    ID,
                                        //                                    CREATE_USER,
                                        //                                    MODIFY_USER,
                                        //                                    STORAGE_LOCATION,
                                        //                                    DOC_CABINET,
                                        //                                    DOC_GRID
                                        //                                    )
                                        //                                    VALUES
                                        //                                    ('{0}','{1}','{1}','{2}','{3}','{4}');
                                        //                                    INSERT INTO erms_arch_form_ru (
                                        //                                    ID,
                                        //                                    CREATE_USER,
                                        //                                    MODIFY_USER,
                                        //                                    ARCH_NO,
                                        //                                    ARCH_NAME,
                                        //                                    ERMS_SECT_ID,
                                        //                                    ERMS_ARCH_TYPE_ID,
                                        //                                    ERMS_DOSSIER_ID,
                                        //                                    ERMS_STORAGE_ID,
                                        //                                    ERMS_AUDIT_TIMES_ID,
                                        //                                    APPLY_LINE,
                                        //                                    CUR_OPER,
                                        //                                    CUR_DATE,
                                        //                                    SAVE_PERIOD,
                                        //                                    SECRET,
                                        //                                    CURRENT_ARCH_NUM,
                                        //                                    INIT_STOCK,
                                        //                                    NOTES
                                        //                                    )
                                        //                                    VALUES
                                        //                                    ('{5}','{1}','{1}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{1}','{14}','{15}','{16}','{17}','{18}','{19}');
                                        //                                    INSERT INTO erms_archived_status_ru (
                                        //                                    ID,
                                        //                                    CREATE_USER,
                                        //                                    MODIFY_USER,
                                        //                                    ERMS_ARCH_FORM_ID,
                                        //                                    ERMS_DOSSIER_ID,
                                        //                                    EVENT_STATUS,
                                        //                                    SAVE_DATE,
                                        //                                    SAVE_OPER,
                                        //                                    ES_HANDLE_STATUS
                                        //                                    )
                                        //                                    VALUES
                                        //                                    ('{20}','{1}','{1}','{5}','{10}','{21}','{14}','{1}','{22}');
                                        //                                    INSERT INTO erms_audit_times (
                                        //                                    ID,
                                        //                                    CREATE_USER,
                                        //                                    MODIFY_USER,
                                        //                                    ERMS_ARCH_FORM_ID,
                                        //                                    ERMS_DOSSIER_ID,
                                        //                                    AUDIT_STATUS,
                                        //                                    APPROVAL_DATE,
                                        //                                    AUDIT_TYPE,
                                        //                                    ARCH_NUM
                                        //                                    )
                                        //                                    VALUES
                                        //                                    ('{12}','{1}','{1}','{5}','{10}','{23}','{14}','{24}','{25}');",
                                        //                                    ERMS_STORAGE_ID, "Admin", list[10], DOC_CABINET, DOC_GRID, ERMS_ARCH_FORM_ID, !string.IsNullOrEmpty(list[2].Trim()) ? list[2] : Guid.NewGuid().ToString(),
                                        //                                    list[5], "1138013309136670720", "1138013519845920768", ERMS_DOSSIER_ID, ERMS_STORAGE_ID, ERMS_AUDIT_TIMES_ID, 50, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                                        //                                    SAVE_PERIOD, SECRET, 1, 0, list[11], ERMS_ARCHIVED_STATUS_ID, 1, -1, 1, 60, 1);
                                        #endregion
                                        insertsql1 = string.Format(@"INSERT INTO erms_storage (
                                                                        ID,
                                                                        CREATE_USER,
                                                                        MODIFY_USER,
                                                                        STORAGE_LOCATION,
                                                                        DOC_CABINET,
                                                                        DOC_GRID
                                                                        )
                                                                        VALUES
                                                                        (@ERMS_STORAGE_ID,@CREATE_USER,@MODIFY_USER,@STORAGE_LOCATION,@DOC_CABINET,@DOC_GRID);
                                                                        INSERT INTO erms_arch_form_ru (
                                                                        ID,
                                                                        CREATE_USER,
                                                                        MODIFY_USER,
                                                                        ARCH_NO,
                                                                        ARCH_NAME,
                                                                        ERMS_SECT_ID,
                                                                        ERMS_ARCH_TYPE_ID,
                                                                        ERMS_DOSSIER_ID,
                                                                        ERMS_STORAGE_ID,
                                                                        ERMS_AUDIT_TIMES_ID,
                                                                        APPLY_LINE,
                                                                        CUR_OPER,
                                                                        CUR_DATE,
                                                                        SAVE_PERIOD,
                                                                        SECRET,
                                                                        CURRENT_ARCH_NUM,
                                                                        INIT_STOCK,
                                                                        NOTES
                                                                        )
                                                                        VALUES
                                                                        (@ERMS_ARCH_FORM_ID,@CREATE_USER,@MODIFY_USER,@ARCH_NO,@ARCH_NAME,@ERMS_SECT_ID,@ERMS_ARCH_TYPE_ID,@ERMS_DOSSIER_ID,@ERMS_STORAGE_ID,@ERMS_AUDIT_TIMES_ID,@APPLY_LINE,@CUR_OPER,@CUR_DATE,@SAVE_PERIOD,@SECRET,@CURRENT_ARCH_NUM,@INIT_STOCK,@NOTES);
                                                                        INSERT INTO erms_archived_status_ru (
                                                                        ID,
                                                                        CREATE_USER,
                                                                        MODIFY_USER,
                                                                        ERMS_ARCH_FORM_ID,
                                                                        ERMS_DOSSIER_ID,
                                                                        EVENT_STATUS,
                                                                        SAVE_DATE,
                                                                        SAVE_OPER,
                                                                        ES_HANDLE_STATUS
                                                                        )
                                                                        VALUES
                                                                        (@ERMS_ARCHIVED_STATUS_ID,CREATE_USER,@MODIFY_USER,@ERMS_ARCH_FORM_ID,@ERMS_DOSSIER_ID,@EVENT_STATUS,@SAVE_DATE,@SAVE_OPER,@ES_HANDLE_STATUS);
                                                                        INSERT INTO erms_audit_times (
                                                                        ID,
                                                                        CREATE_USER,
                                                                        MODIFY_USER,
                                                                        ERMS_ARCH_FORM_ID,
                                                                        ERMS_DOSSIER_ID,
                                                                        AUDIT_STATUS,
                                                                        APPROVAL_DATE,
                                                                        AUDIT_TYPE,
                                                                        ARCH_NUM
                                                                        )
                                                                        VALUES
                                                                        (@ERMS_AUDIT_TIMES_ID,@CREATE_USER,@MODIFY_USER,@ERMS_ARCH_FORM_ID,@ERMS_DOSSIER_ID,@AUDIT_STATUS,@APPROVAL_DATE,@AUDIT_TYPE,@ARCH_NUM);");
                                        DynamicParameters param = new DynamicParameters();
                                        param.Add("STORAGE_LOCATION", STORAGE_LOCATION);
                                        param.Add("DOC_CABINET", DOC_CABINET);
                                        param.Add("DOC_GRID", DOC_GRID);
                                        param.Add("ERMS_ARCH_FORM_ID", ERMS_ARCH_FORM_ID);
                                        param.Add("CREATE_USER", "Admin");
                                        param.Add("MODIFY_USER", "Admin");
                                        param.Add("ARCH_NO", !string.IsNullOrEmpty(list[2].Trim()) ? list[2] : Guid.NewGuid().ToString());
                                        param.Add("ARCH_NAME", list[5]);
                                        param.Add("ERMS_SECT_ID", "1138013309136670720");
                                        param.Add("ERMS_ARCH_TYPE_ID", "1138013519845920768");
                                        param.Add("ERMS_DOSSIER_ID", ERMS_DOSSIER_ID);
                                        param.Add("ERMS_STORAGE_ID", ERMS_STORAGE_ID);
                                        param.Add("ERMS_AUDIT_TIMES_ID", ERMS_AUDIT_TIMES_ID);
                                        param.Add("APPLY_LINE", 50);
                                        param.Add("CUR_OPER", "Admin");
                                        param.Add("CUR_DATE", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                                        param.Add("SAVE_PERIOD", SAVE_PERIOD);
                                        param.Add("SECRET", SECRET);
                                        param.Add("CURRENT_ARCH_NUM", 1);
                                        param.Add("INIT_STOCK", 0);
                                        param.Add("NOTES", list[11]);
                                        param.Add("ERMS_ARCHIVED_STATUS_ID", ERMS_ARCHIVED_STATUS_ID);
                                        param.Add("EVENT_STATUS", 1);
                                        param.Add("SAVE_DATE", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                                        param.Add("SAVE_OPER", "Admin");
                                        param.Add("ES_HANDLE_STATUS", -1);
                                        param.Add("ERMS_AUDIT_TIMES_ID", ERMS_AUDIT_TIMES_ID);
                                        param.Add("AUDIT_STATUS", 1);
                                        param.Add("APPROVAL_DATE", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                                        param.Add("AUDIT_TYPE", 60);
                                        param.Add("ARCH_NUM", 1);
                                        tag = sqlhelper.ExcuteNonQuery(insertsql1, param);

                                    }
                                    //DynamicParameters param = new DynamicParameters();
                                    //int tag = sqlhelper.ExcuteNonQuery(insertsql, param);
                                    if (tag < 1)
                                    {
                                        log.log("第" + i + "行导入失败！");
                                    }
                                    Model m = new Model();
                                    m.CREATETIME = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                                    m.STATUS = (tag > 0 ? "成功" : "失败");
                                    m.ID = list[0];
                                    m.NAME = list[1];
                                    Result r = new Result();
                                    r.m = m;
                                    r.count = i.ToString();
                                    worker.ReportProgress(percent, r);
                                }
                                catch (Exception ex)
                                {
                                    log.log("第" + i + "行导入失败！" + ex.Message);
                                    Model m = new Model();
                                    m.CREATETIME = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                                    m.STATUS = "失败";
                                    m.ID = list[0];
                                    m.NAME = list[1];
                                    Result r = new Result();
                                    r.m = m;
                                    r.count = i.ToString();
                                    worker.ReportProgress(percent, r);

                                }
                                //worker.ReportProgress(percent);
                                //Thread.Sleep(100);

                                continue;
                            }
                        }
                        log.log("导入完成！");
                        break;
                }
            }
            catch (Exception ex)
            {
                log.log(ex.Message);
                //throw new Exception(ex.Message);
            }

            return maxRecords;
        }
    }
}
